package com.personnel.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.personnel.db.C3p0Util1;
import com.personnel.entity.User;


public class UserDao implements IUserDao
{
	Connection conn=null;
    PreparedStatement pstm=null;
    ResultSet re=null;
	@Override
	public boolean add(User user) {
       
        try {
            conn =(Connection) C3p0Util1.getConn();
            //连接数据库，调用DbConn中的连接方法
            String sql=" INSERT INTO USERS(name,pwd) VALUES(?,?) ";
            pstm = (PreparedStatement) conn.prepareStatement(sql);
            //执行sql语句
            pstm.setObject(1,user.getUserName());
            pstm.setObject(2,user.getUserPwd());
            
            return pstm.executeUpdate()>0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally{
        	C3p0Util1.close(pstm);
        	C3p0Util1.close(conn);
            //关闭接口
        }
	}

	@Override
	public boolean del(String userName)
	{
		try
		{
			conn=(Connection) C3p0Util1.getConn();
			String sql=" DELETE FROM USERS WHERE name=? ";
			pstm=(PreparedStatement) conn.prepareStatement(sql);
			pstm.setObject(1, userName);
			return pstm.executeUpdate()>0;
		} catch (SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			C3p0Util1.close(pstm);
			C3p0Util1.close(conn);
		}
	}

	@Override
	public boolean upd(User user)
	{
		try
		{
			conn=(Connection) C3p0Util1.getConn();
			String sql=" UPDATE USERS SET pwd=? WHERE name=? ";
			pstm= (PreparedStatement) conn.prepareStatement(sql);
			pstm.setObject(1, user.getUserPwd());
			pstm.setObject(2, user.getUserName());
			return pstm.executeUpdate()>0;
		} catch (SQLException e)
		{
			e.printStackTrace();
			return false;
		}finally{
			C3p0Util1.close(conn);
			C3p0Util1.close(pstm);
		}
		
	}

	@Override
	public User query(String userName, String userPwd)
	{
		try
		{
			conn=(Connection) C3p0Util1.getConn();
			String sql=" SELECT * FROM USERS WHERE name=? AND pwd=? ";
			pstm=(PreparedStatement) conn.prepareStatement(sql);
			pstm.setObject(1, userName);
			pstm.setObject(2, userPwd);
			
			re=pstm.executeQuery();
			User user=null;
			while(re.next()){
				user=new User();
				user.setId(re.getInt("id"));
				user.setUserName(re.getString("name"));
				user.setUserPwd(re.getString("pwd"));	
					
			}
			return user;
		} catch (SQLException e)
		{
			e.printStackTrace();
			return null;
		}finally{
			C3p0Util1.close(re);
			C3p0Util1.close(pstm);
			C3p0Util1.close(conn);
        }
		
	}

	@Override
	public List<User> queryAll()
	{
		
		 try {
	            conn = (Connection) C3p0Util1.getConn();
	            String sql=" SELECT * FROM USERS ";
	            pstm=(PreparedStatement) conn.prepareStatement(sql);

	            re=pstm.executeQuery();
	            //ResultSet接口的对象 查询时调用
	            List<User> list=new ArrayList<>();
	            User user=null;
	            while(re.next()){
	                user=new User();
	                int usid=re.getInt("id");
	                String usname=re.getString("name");
	                String uspassword=re.getString("pwd");
	              
	                user.setId(usid);
	                user.setUserName(usname);
	                user.setUserPwd(uspassword);
	                list.add(user);
	            }
	            return list;
	        } catch (SQLException e) {
	            e.printStackTrace();
	            return null;
	        }finally{
	        	C3p0Util1.close(re);
	        	C3p0Util1.close(pstm);
	        	C3p0Util1.close(conn);
	        }
	}

	@Override
	public User queryname(String userName)
	{
		try
		{
			conn=(Connection) C3p0Util1.getConn();
			String sql=" SELECT * FROM USERS WHERE name=? ";
			pstm=(PreparedStatement) conn.prepareStatement(sql);
			pstm.setObject(1, userName);	
			re=pstm.executeQuery();
			User user=null;
			while(re.next()){
				user=new User();
				user.setId(re.getInt("id"));
				user.setUserName(re.getString("name"));
				user.setUserPwd(re.getString("pwd"));	
			}
			return user;
		} catch (SQLException e)
		{
			e.printStackTrace();
			return null;
		}finally{
			C3p0Util1.close(re);
			C3p0Util1.close(pstm);
			C3p0Util1.close(conn);
        }
	}

}
